
//
// 120228 - AH - (Created).
//

package org.japril.gim.sql;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Properties;

import org.japril.gim.Item;
import org.japril.gim.ItemItem;
import org.japril.gim.Items;
import org.japril.jutil.CommandLineParser;

/**
 * <p>
 *   Object that creates tables for a SQL implementation of General Item Model
 *   (GIM).
 * </p>
 * @author Arne Halvorsen (AH)
 */
public class Tables 
{
  private final Connection m_con;
  
  /**
   * <p>
   *   Constructor.
   * </p>
   * @param c Database connection.
   */
  public Tables(Connection c)
  {
    if (c==null)
    {
      throw new NullPointerException("c");
    }
    
    m_con=c;  
  }
  
  public static final class DbDef
  {
    public final String table;
    public final String sql;
    
    private DbDef(String table, String sql){ this.table=table; this.sql=sql; }
    
    @Override
    public String toString()
    {
      StringBuilder sb=new StringBuilder();
      
      sb.append("table: ").append(table).append(" sql: "+sql);
      
      return sb.toString();
    }
  }
  
  private static DbDef[] getCreateDbDefs()
  {
    List<DbDef> sqlst=new ArrayList<DbDef>();
    
    StringBuilder sb=new StringBuilder();
    
    String table="propertydef";
    
    sb.setLength(0);
    sb.append("create table ").append(table)
      .append(" (name varchar(32) not null primary key, ")
      .append("ftype varchar(32) not null, flen int not null)");
    String sql=sb.toString();
    sqlst.add(new DbDef(table, sql));
    
    table="item";
    
    sb.setLength(0);
    sb.append("create table ").append(table)
      .append(" (id int not null primary key auto_increment, ")
      .append("type varchar(256) not null)");
    sql=sb.toString();
    sqlst.add(new DbDef(table, sql));
    
    table="itemitem";
    
    sb.setLength(0);
    sb.append("create table ").append(table)
      .append(" (lid int not null, ltype varchar(256) not null, ")
      .append("rid int not null, rtype varchar(256) not null)");
    sql=sb.toString();
    sqlst.add(new DbDef(table, sql));
    
    sb.setLength(0);
    sb.append("create index idx0 on ").append(table)
      .append(" (lid, ltype, rid, rtype)");
    sql=sb.toString();
    sqlst.add(new DbDef(null, sql));
    
    return sqlst.toArray(new DbDef[0]);
  }
  
  /**
   * <p>
   *   Creates tables.
   * </p>
   * @throws IOException If fails.
   */
  public final void create() throws IOException
  {
    DbDef[] defs=getCreateDbDefs();
    
    Statement st=null;
    try
    {
      dropTables(defs);
      
      st=m_con.createStatement();
      
      for (DbDef dbDef : defs)
      {
        st.executeUpdate(dbDef.sql);
      }
    }
    catch (SQLException sqlx)
    {
      IOException iox=new IOException();
      iox.initCause(sqlx);
      throw iox;
    }
    finally
    {
      if (st!=null) try { st.close(); } catch (SQLException _){}
    }
  }
  
  private void dropTable(Statement st, StringBuilder sb, String table)
  {
    sb=(sb==null) ? new StringBuilder() : sb;
    
    try
    {
      sb.setLength(0);
      sb.append("drop table "+table);
      String sql=sb.toString();
      st.executeUpdate(sql);
    }
    catch (SQLException sqlx){}
  }
  
  public void dropTables(DbDef[] defs) throws IOException
  {
    if (defs==null)
    {
      throw new NullPointerException("defs");
    }
    
    Statement st=null;
    try
    {
      st=m_con.createStatement();
      
      for (DbDef dbDef : defs)
      {
        String table=dbDef.table;
        if (table!=null) dropTable(st, null, table);
      }
    }
    catch (SQLException sqlx)
    {
      IOException iox=new IOException();
      iox.initCause(sqlx);
      throw iox;
    }
    finally
    {
      if (st!=null) try { st.close(); } catch (SQLException _){}
    }
  }
  
  public static void main(String[] args)
  {
    DbDef[] defs=getCreateDbDefs();
    for (DbDef dbDef : defs) 
    {
      System.out.println(dbDef);  
    }
  }
  
  /*
  public static void main(String[] args)
  {
    CommandLineParser clp=new CommandLineParser(args);
    
    String user=clp.value("-user", "auser");
    String pass=clp.value("-pass");
    
    try
    {
      Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
      System.out.println("Load the embedded driver");
      Connection conn = null;
      Properties props = new Properties();
      props.put("user", user);
      if (pass!=null) props.put("password", pass);
      // create and connect the database named helloDB 
      conn=DriverManager.getConnection("jdbc:derby:helloDB;create=true", props);
      System.out.println("create and connect to helloDB");
      conn.setAutoCommit(true);
      
      Tables ct=new Tables(conn);
      
      ct.dropTables();
      
      //System.exit(0);
      
      ct.create();
      
      Items items=new SqlItems(conn);
      
      items.defineProperty("name", "varchar", 10, null);
      
      Item item1=items.create();
      System.out.println(item1);
      
      items.setProperty(item1, "name", "Item 1");
      String name=items.getStringProperty(item1, "name");
      System.out.println("Item1's name: "+name);
      
      Item item2=items.create();
      System.out.println(item2);
      
      System.out.println("Item: "+item1+" exists: "+
        items.exists(item1.getId()));
      
      System.out.println("Item: "+item2+" exists: "+
        items.exists(item2.getId()));
      
      System.out.println("Item: "+99+" exists: "+
        items.exists(99));
      
      System.out.println("All items");
      Collection<Item> ims=items.getItems();
      for (Item item : ims)
      {
        System.out.println("  "+item);  
      }
      
      items.createRelation(item1.getId(), item2.getId());
      
      Collection<ItemItem> rel=items.getRelations();
      System.out.println("all relations");
      for (ItemItem curr : rel) 
      {
        System.out.println("  "+curr);  
      }
      
      Item ritem=items.getRightItem(item1);
      System.out.println("right item of "+item1+" : "+ritem);
      
      Collection<Item> ritems=items.getRightItems(item1);
      System.out.println("right items of "+item1+" : "+ritems);
      
      Item litem=items.getLeftItem(item2);
      System.out.println("left item of "+item2+" : "+litem);
      
      Collection<Item> litems=items.getLeftItems(item2);
      System.out.println("left items of "+item2+" : "+litems);
      
      if (items.deleteRelation(item1, item2))
      {
        System.out.println("Did delete relation");
      }
      else
      {
        System.out.println("Did NOT delete relation");
      }
      
      rel=items.getRelations();
      System.out.println("all relations after delete of relation");
      for (ItemItem curr : rel) 
      {
        System.out.println("  "+curr);  
      }
      
      System.exit(0);
    }
    catch (Exception x)
    {
      x.printStackTrace();
      System.exit(-1);
    }
  }
  */
  
}
